package com.bcb.dao;

import com.bcb.model.Share;
import my.bcb.util.DBConnection;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import my.bcb.util.TimeUtils;

public class ShareDAO {
    
    /**
     * 创建共享
     */
	// 4. 修改createShare方法，添加时区调试信息
	public boolean createShare(Share share) {
	    // 添加时区调试信息
	    System.out.println(String.format("[DEBUG] 当前JVM时区: %s", java.util.TimeZone.getDefault().getID()));
	    System.out.println(String.format("[DEBUG] 当前系统时间: %s", LocalDateTime.now()));
	    System.out.println(String.format("[DEBUG] 当前UTC时间: %s", java.time.Instant.now()));
	    
	    String sql = "INSERT INTO shares (owner_id, resource_type, resource_id, share_type, " +
	                "target_id, permission_level, expires_at, resource_name) " +
	                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	    
	    try (Connection conn = DBConnection.getConnection();
	         PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
	        
	        stmt.setInt(1, share.getOwnerId());
	        stmt.setString(2, share.getResourceType().name().toLowerCase());
	        stmt.setInt(3, share.getResourceId());
	        stmt.setString(4, share.getShareType().name().toLowerCase());
	        
	        if (share.getTargetId() != null) {
	            stmt.setInt(5, share.getTargetId());
	        } else {
	            stmt.setNull(5, Types.INTEGER);
	        }
	        
	        stmt.setString(6, share.getPermissionLevel().name().toLowerCase());
	        
	        if (share.getExpiresAt() != null) {
	            Timestamp expiresTimestamp = Timestamp.valueOf(share.getExpiresAt());
	            stmt.setTimestamp(7, expiresTimestamp);
	            
	            System.out.println(String.format("[DEBUG] 存储过期时间: LocalDateTime=%s, Timestamp=%s", 
	                share.getExpiresAt(), expiresTimestamp));
	        } else {
	            stmt.setNull(7, Types.TIMESTAMP);
	        }
	        
	        stmt.setString(8, share.getResourceName());
	        
	        int result = stmt.executeUpdate();
	        if (result > 0) {
	            ResultSet generatedKeys = stmt.getGeneratedKeys();
	            if (generatedKeys.next()) {
	                share.setShareId(generatedKeys.getInt(1));
	            }
	            return true;
	        }
	    } catch (SQLException e) {
	        System.err.println("创建共享失败: " + e.getMessage());
	        e.printStackTrace();
	    }
	    return false;
	}
    
    /**
     * 获取用户创建的共享列表
     */
 // 2. 修改getSharesByOwner方法，添加详细调试
    public List<Share> getSharesByOwner(int ownerId) {
        System.out.println(String.format("[DEBUG] getSharesByOwner - 查询用户ID: %d", ownerId));
        
        String sql = "SELECT s.*, " +
                    "CASE " +
                    "  WHEN s.share_type = 'user' THEN u.username " +
                    "  WHEN s.share_type = 'group' THEN ug.group_name " +
                    "  ELSE '公开共享' " +
                    "END as target_name " +
                    "FROM shares s " +
                    "LEFT JOIN users u ON s.share_type = 'user' AND s.target_id = u.user_id " +
                    "LEFT JOIN user_groups ug ON s.share_type = 'group' AND s.target_id = ug.group_id " +
                    "WHERE s.owner_id = ? AND s.is_active = TRUE " +
                    "ORDER BY s.created_at DESC";
        
        List<Share> shares = new ArrayList<>();
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, ownerId);
            ResultSet rs = stmt.executeQuery();
            
            int count = 0;
            while (rs.next()) {
                count++;
                
                // 🔧 详细的原始数据调试
                System.out.println(String.format("\n[DEBUG] === 原始数据库记录 %d ===", count));
                System.out.println("share_id: " + rs.getInt("share_id"));
                System.out.println("resource_name: " + rs.getString("resource_name"));
                System.out.println("target_name: " + rs.getString("target_name"));
                
                // 关键：原始时间戳数据
                Timestamp expiresAtTimestamp = rs.getTimestamp("expires_at");
                Timestamp createdAtTimestamp = rs.getTimestamp("created_at");
                
                System.out.println("expires_at (Timestamp): " + expiresAtTimestamp);
                System.out.println("created_at (Timestamp): " + createdAtTimestamp);
                
                if (expiresAtTimestamp != null) {
                    System.out.println("expires_at (toString): " + expiresAtTimestamp.toString());
                    System.out.println("expires_at (getTime): " + expiresAtTimestamp.getTime());
                    LocalDateTime expiresLocal = expiresAtTimestamp.toLocalDateTime();
                    System.out.println("expires_at (LocalDateTime): " + expiresLocal);
                }
                
                if (createdAtTimestamp != null) {
                    System.out.println("created_at (toString): " + createdAtTimestamp.toString());
                    LocalDateTime createdLocal = createdAtTimestamp.toLocalDateTime();
                    System.out.println("created_at (LocalDateTime): " + createdLocal);
                }
                
                Share share = mapResultSetToShare(rs);
                
                // 🔧 映射后的对象调试
                System.out.println("\n[DEBUG] === 映射后的Share对象 ===");
                System.out.println("Share.getExpiresAt(): " + share.getExpiresAt());
                System.out.println("Share.getCreatedAt(): " + share.getCreatedAt());
                
                shares.add(share);
            }
            
            System.out.println(String.format("\n[DEBUG] 总共查询到 %d 条记录", count));
            
        } catch (SQLException e) {
            System.err.println("获取用户共享列表失败: " + e.getMessage());
            e.printStackTrace();
        }
        return shares;
    }
    
    /**
     * 获取用户可访问的共享资源 - 修复版
     */
    public List<Share> getAccessibleShares(int userId) {
        System.out.println(String.format("[DEBUG] getAccessibleShares - 当前用户ID: %d", userId));
        System.out.println(String.format("[DEBUG] 当前服务器时间: %s", LocalDateTime.now()));
        
        // 分步查询，更清晰的逻辑
        String sql = "SELECT DISTINCT s.*, u.username as owner_name " +
                    "FROM shares s " +
                    "INNER JOIN users u ON s.owner_id = u.user_id " +
                    "WHERE s.is_active = 1 " +
                    "AND s.owner_id != ? " +  // 排除自己创建的共享
                    "AND (s.expires_at IS NULL OR s.expires_at > NOW()) " +
                    "AND (" +
                    "  s.share_type = 'public' OR " +
                    "  (s.share_type = 'user' AND s.target_id = ?) OR " +
                    "  (s.share_type = 'group' AND s.target_id IN (" +
                    "    SELECT ugm.group_id FROM user_group_mappings ugm WHERE ugm.user_id = ?" +
                    "  ))" +
                    ") " +
                    "ORDER BY s.created_at DESC";
        
        System.out.println("[DEBUG] 执行SQL: " + sql);
        System.out.println(String.format("[DEBUG] 参数: userId1=%d, userId2=%d, userId3=%d", userId, userId, userId));
        
        List<Share> shares = new ArrayList<>();
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, userId);  // 排除自己的共享
            stmt.setInt(2, userId);  // 用户共享目标
            stmt.setInt(3, userId);  // 用户组成员
            
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                Share share = mapResultSetToShare(rs);
                shares.add(share);
                
                System.out.println(String.format("[DEBUG] 找到有效共享: ID=%d, 资源=%s, 过期时间=%s", 
                    share.getShareId(), 
                    share.getResourceName(), 
                    share.getExpiresAt()));
            }
            
            System.out.println(String.format("[DEBUG] 总共找到 %d 个有效共享", shares.size()));
            
            // 如果没有找到共享，进行调试分析
            if (shares.size() == 0) {
                debugAllShares(conn, userId);
            }
            
        } catch (SQLException e) {
            System.err.println("[ERROR] 获取可访问共享失败: " + e.getMessage());
            e.printStackTrace();
        }
        return shares;
    }
    
    

/**
 * 根据ID获取共享记录
 */
public Share getShareById(int shareId) {
    String sql = "SELECT s.*, u.username as owner_name, " +
                "CASE " +
                "  WHEN s.share_type = 'user' THEN u2.username " +
                "  WHEN s.share_type = 'group' THEN ug.group_name " +
                "  ELSE '公开共享' " +
                "END as target_name " +
                "FROM shares s " +
                "JOIN users u ON s.owner_id = u.user_id " +
                "LEFT JOIN users u2 ON s.share_type = 'user' AND s.target_id = u2.user_id " +
                "LEFT JOIN user_groups ug ON s.share_type = 'group' AND s.target_id = ug.group_id " +
                "WHERE s.share_id = ?";
    
    try (Connection conn = DBConnection.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        
        stmt.setInt(1, shareId);
        ResultSet rs = stmt.executeQuery();
        
        if (rs.next()) {
            return mapResultSetToShare(rs);
        }
    } catch (SQLException e) {
        System.err.println("根据ID获取共享记录失败: " + e.getMessage());
        e.printStackTrace();
    }
    return null;
}

/**
 * 续期共享 - 更新过期时间
 */
public boolean renewShare(int shareId, LocalDateTime newExpiresAt) {
    String sql = "UPDATE shares SET expires_at = ?, updated_at = NOW() WHERE share_id = ?";
    
    try (Connection conn = DBConnection.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        
        if (newExpiresAt != null) {
            stmt.setTimestamp(1, Timestamp.valueOf(newExpiresAt));
            System.out.println(String.format("[DEBUG] 设置新过期时间: %s", 
                TimeUtils.formatLocalDateTime(newExpiresAt)));
        } else {
            stmt.setNull(1, Types.TIMESTAMP);
            System.out.println("[DEBUG] 设置为永不过期");
        }
        
        stmt.setInt(2, shareId);
        
        int rowsAffected = stmt.executeUpdate();
        System.out.println(String.format("[DEBUG] 续期更新影响行数: %d", rowsAffected));
        
        return rowsAffected > 0;
    } catch (SQLException e) {
        System.err.println("续期共享失败: " + e.getMessage());
        e.printStackTrace();
    }
    return false;
}
    
    
    /**
     * 调试方法：检查所有共享记录
     */
    private void debugAllShares(Connection conn, int currentUserId) {
        try {
            System.out.println("[DEBUG] === 调试所有共享记录 ===");
            
            String debugSql = "SELECT s.*, u.username as owner_name FROM shares s " +
                             "JOIN users u ON s.owner_id = u.user_id " +
                             "WHERE s.is_active = 1 ORDER BY s.created_at DESC";
            
            try (PreparedStatement stmt = conn.prepareStatement(debugSql)) {
                ResultSet rs = stmt.executeQuery();
                
                while (rs.next()) {
                    int shareId = rs.getInt("share_id");
                    int ownerId = rs.getInt("owner_id");
                    String shareType = rs.getString("share_type");
                    Integer targetId = rs.getObject("target_id", Integer.class);
                    String resourceName = rs.getString("resource_name");
                    String ownerName = rs.getString("owner_name");
                    
                    System.out.println(String.format("[DEBUG] 共享记录: ID=%d, Owner=%s(%d), Type=%s, Target=%s, Resource=%s", 
                        shareId, ownerName, ownerId, shareType, targetId, resourceName));
                    
                    // 检查为什么这个共享不能被当前用户访问
                    if (ownerId != currentUserId) {
                        System.out.println("[DEBUG]   -> 不是自己的共享，检查访问权限...");
                        
                        if ("public".equals(shareType)) {
                            System.out.println("[DEBUG]   -> 公开共享，应该可访问");
                        } else if ("user".equals(shareType)) {
                            if (targetId != null && targetId.equals(currentUserId)) {
                                System.out.println("[DEBUG]   -> 用户共享且目标是当前用户，应该可访问");
                            } else {
                                System.out.println("[DEBUG]   -> 用户共享但目标不是当前用户(target=" + targetId + ", current=" + currentUserId + ")");
                            }
                        } else if ("group".equals(shareType)) {
                            // 检查用户组成员关系
                            checkUserGroupMembership(conn, currentUserId, targetId);
                        }
                    } else {
                        System.out.println("[DEBUG]   -> 这是自己的共享，已排除");
                    }
                }
            }
            
            System.out.println("[DEBUG] === 调试结束 ===");
            
        } catch (SQLException e) {
            System.err.println("[DEBUG] 调试查询失败: " + e.getMessage());
        }
    }
    
    /**
     * 检查用户组成员关系
     */
    private void checkUserGroupMembership(Connection conn, int userId, Integer groupId) {
        try {
            String sql = "SELECT * FROM user_group_mappings WHERE user_id = ? AND group_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setInt(1, userId);
                stmt.setInt(2, groupId != null ? groupId : -1);
                
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    System.out.println("[DEBUG]   -> 用户在组 " + groupId + " 中，应该可访问");
                } else {
                    System.out.println("[DEBUG]   -> 用户不在组 " + groupId + " 中");
                }
            }
        } catch (SQLException e) {
            System.err.println("[DEBUG] 检查用户组关系失败: " + e.getMessage());
        }
    }
    
    /**
     * 检查用户对资源的共享权限
     */
    public Share getUserSharePermission(int userId, Share.ResourceType resourceType, int resourceId) {
        String sql = "SELECT DISTINCT s.*, u.username as owner_name " +
                    "FROM shares s " +
                    "JOIN users u ON s.owner_id = u.user_id " +
                    "WHERE s.resource_type = ? AND s.resource_id = ? AND s.is_active = TRUE AND " +
                    "(" +
                    "  s.share_type = 'public' OR " +
                    "  (s.share_type = 'user' AND s.target_id = ?) OR " +
                    "  (s.share_type = 'group' AND s.target_id IN (" +
                    "    SELECT ugm.group_id FROM user_group_mappings ugm WHERE ugm.user_id = ?" +
                    "  ))" +
                    ") " +
                    "AND (s.expires_at IS NULL OR s.expires_at > NOW()) " +
                    "ORDER BY s.permission_level DESC LIMIT 1";  // 优先返回编辑权限
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, resourceType.name().toLowerCase());
            stmt.setInt(2, resourceId);
            stmt.setInt(3, userId);
            stmt.setInt(4, userId);
            
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToShare(rs);
            }
        } catch (SQLException e) {
            System.err.println("检查用户共享权限失败: " + e.getMessage());
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 撤销共享
     */
    public boolean revokeShare(int shareId, int ownerId) {
        String sql = "UPDATE shares SET is_active = FALSE, updated_at = NOW() " +
                    "WHERE share_id = ? AND owner_id = ?";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, shareId);
            stmt.setInt(2, ownerId);
            
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("撤销共享失败: " + e.getMessage());
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * 获取资源的所有共享记录
     */
    public List<Share> getResourceShares(Share.ResourceType resourceType, int resourceId) {
        String sql = "SELECT s.*, u.username as owner_name, " +
                    "CASE " +
                    "  WHEN s.share_type = 'user' THEN u2.username " +
                    "  WHEN s.share_type = 'group' THEN ug.group_name " +
                    "  ELSE '公开共享' " +
                    "END as target_name " +
                    "FROM shares s " +
                    "JOIN users u ON s.owner_id = u.user_id " +
                    "LEFT JOIN users u2 ON s.share_type = 'user' AND s.target_id = u2.user_id " +
                    "LEFT JOIN user_groups ug ON s.share_type = 'group' AND s.target_id = ug.group_id " +
                    "WHERE s.resource_type = ? AND s.resource_id = ? AND s.is_active = TRUE " +
                    "ORDER BY s.created_at DESC";
        
        List<Share> shares = new ArrayList<>();
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, resourceType.name().toLowerCase());
            stmt.setInt(2, resourceId);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                shares.add(mapResultSetToShare(rs));
            }
        } catch (SQLException e) {
            System.err.println("获取资源共享记录失败: " + e.getMessage());
            e.printStackTrace();
        }
        return shares;
    }
    
    /**
     * 清理过期的共享
     */
    public int cleanExpiredShares() {
        String sql = "UPDATE shares SET is_active = FALSE, updated_at = NOW() " +
                    "WHERE expires_at IS NOT NULL AND expires_at <= NOW() AND is_active = TRUE";
        
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            return stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("清理过期共享失败: " + e.getMessage());
            e.printStackTrace();
        }
        return 0;
    }
    
    /**
     * 获取资源的共享用户列表（用于继承权限）
     */
    public List<Integer> getSharedUserIds(Share.ResourceType resourceType, int resourceId) {
        String sql = "SELECT DISTINCT " +
                    "CASE " +
                    "  WHEN s.share_type = 'user' THEN s.target_id " +
                    "  WHEN s.share_type = 'group' THEN ugm.user_id " +
                    "  ELSE NULL " +
                    "END as user_id " +
                    "FROM shares s " +
                    "LEFT JOIN user_group_mappings ugm ON s.share_type = 'group' AND s.target_id = ugm.group_id " +
                    "WHERE s.resource_type = ? AND s.resource_id = ? AND s.is_active = TRUE " +
                    "AND (s.expires_at IS NULL OR s.expires_at > NOW()) " +
                    "AND user_id IS NOT NULL";
        
        List<Integer> userIds = new ArrayList<>();
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, resourceType.name().toLowerCase());
            stmt.setInt(2, resourceId);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                userIds.add(rs.getInt("user_id"));
            }
        } catch (SQLException e) {
            System.err.println("获取共享用户列表失败: " + e.getMessage());
            e.printStackTrace();
        }
        return userIds;
    }
    
    /**
     * 将结果集映射为Share对象
     */
 // 3. 修改mapResultSetToShare方法，添加详细调试
    private Share mapResultSetToShare(ResultSet rs) throws SQLException {
        Share share = new Share();
        
        int shareId = rs.getInt("share_id");
        share.setShareId(shareId);
        System.out.println(String.format("[DEBUG] 处理Share ID: %d", shareId));
        
        share.setOwnerId(rs.getInt("owner_id"));
        share.setResourceType(Share.ResourceType.valueOf(rs.getString("resource_type").toUpperCase()));
        share.setResourceId(rs.getInt("resource_id"));
        share.setShareType(Share.ShareType.valueOf(rs.getString("share_type").toUpperCase()));
        
        int targetId = rs.getInt("target_id");
        if (!rs.wasNull()) {
            share.setTargetId(targetId);
        }
        
        share.setPermissionLevel(Share.PermissionLevel.valueOf(rs.getString("permission_level").toUpperCase()));
        
        // 🔧 关键：时间字段处理，确保不进行时区转换
        Timestamp expiresAt = rs.getTimestamp("expires_at");
        if (expiresAt != null) {
            // 直接使用Timestamp的toLocalDateTime方法，不进行时区转换
            LocalDateTime expiresLocalDateTime = expiresAt.toLocalDateTime();
            share.setExpiresAt(expiresLocalDateTime);
            
            System.out.println(String.format("[DEBUG] Share %d expires_at 处理:", shareId));
            System.out.println(String.format("  原始Timestamp: %s", expiresAt));
            System.out.println(String.format("  转换LocalDateTime: %s", expiresLocalDateTime));
            System.out.println(String.format("  LocalDateTime.toString(): %s", expiresLocalDateTime.toString()));
        }
        
        share.setActive(rs.getBoolean("is_active"));
        share.setResourceName(rs.getString("resource_name"));
        
        Timestamp createdAt = rs.getTimestamp("created_at");
        if (createdAt != null) {
            LocalDateTime createdLocalDateTime = createdAt.toLocalDateTime();
            share.setCreatedAt(createdLocalDateTime);
            
            System.out.println(String.format("[DEBUG] Share %d created_at 处理:", shareId));
            System.out.println(String.format("  原始Timestamp: %s", createdAt));
            System.out.println(String.format("  转换LocalDateTime: %s", createdLocalDateTime));
        }
        
        Timestamp updatedAt = rs.getTimestamp("updated_at");
        if (updatedAt != null) {
            share.setUpdatedAt(updatedAt.toLocalDateTime());
        }
        
        // 设置关联信息
        try {
            share.setOwnerName(rs.getString("owner_name"));
        } catch (SQLException ignored) {}
        
        try {
            share.setTargetName(rs.getString("target_name"));
        } catch (SQLException ignored) {}
        
        return share;
    }
    
    /**
     * 在事务中创建共享
     */
    public boolean createShareInTransaction(Connection conn, Share share) throws SQLException {
        String sql = "INSERT INTO shares (owner_id, resource_type, resource_id, share_type, " +
                    "target_id, permission_level, expires_at, resource_name, is_active, created_at) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, TRUE, NOW())";
        
        try (PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setInt(1, share.getOwnerId());
            stmt.setString(2, share.getResourceType().name().toLowerCase());
            stmt.setInt(3, share.getResourceId());
            stmt.setString(4, share.getShareType().name().toLowerCase());
            
            if (share.getTargetId() != null) {
                stmt.setInt(5, share.getTargetId());
            } else {
                stmt.setNull(5, Types.INTEGER);
            }
            
            stmt.setString(6, share.getPermissionLevel().name().toLowerCase());
            
            if (share.getExpiresAt() != null) {
                stmt.setTimestamp(7, Timestamp.valueOf(share.getExpiresAt()));
            } else {
                stmt.setNull(7, Types.TIMESTAMP);
            }
            
            stmt.setString(8, share.getResourceName());
            
            int result = stmt.executeUpdate();
            
            if (result > 0) {
                ResultSet generatedKeys = stmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    share.setShareId(generatedKeys.getInt(1));
                }
                return true;
            }
        }
        
        return false;
    }
}